Procedure

Click on the stages below:

function checkParticipantValidityAndSendMail() {
  function getIsNewParticipant(emailSent) {
    return (
      emailSent !== 'PAST_PAR' &&
      emailSent !== 'EMAIL_STUDENT' &&
      emailSent !== 'EMAIL_SENT'
    );
  }

  function getSecondSheetData() {
    var sheet2 = SpreadsheetApp.getActiveSpreadsheet(); 
    SpreadsheetApp.setActiveSheet(sheet2.getSheetByName('Past_Par'))
    var dataRange2 = sheet2.getRange('Past_Par!A1:B4000'); // sheet 2 range
    return dataRange2.getValues();
  }

  function getHasParticipantBeenInPreviousExperiment(
    firstSheetParticipantIndex,
    emailAddress,
    idNumber,
  ) {
    const secondSheetParticipantsData = getSecondSheetData();
    for(let secondSheetParticipant of secondSheetParticipantsData) {
      if (secondSheetParticipant[0] === emailAddress) {
        var colorback = sheet.getRange(startRow + firstSheetParticipantIndex, 1, 1, 10); 
        colorback.setBackground("yellow"); // color row in yellow
        sheet.getRange(startRow + firstSheetParticipantIndex, 10).setValue(pastPar); // Prevents double checkups
        return true;
      }

      if (secondSheetParticipant[1] == idNumber) { 
        var colorback = sheet.getRange(startRow + firstSheetParticipantIndex, 1, 1, 10);
        colorback.setBackground("yellow");
        sheet.getRange(startRow + firstSheetParticipantIndex, 10).setValue(pastPar);
        return true;
      }
    }
  }

  function sendEmail(student, emailAddress, firstSheetParticipantIndex) {
    if (student === "????") {
      lastInsertedSubjectId++;
      const subject = 'Research participation';
      const message =  participantData[0][2] + lastInsertedSubjectId + participantData[0][3] + participantData[0][4] + lastInsertedSubjectId + participantData[0][5]
      sheet.getRange(startRow + firstSheetParticipantIndex, 1).setValue(String(lastInsertedSubjectId));
      sheet.getRange(startRow, 1).setValue(String(lastInsertedSubjectId)); // update the last ID number in cell A1
      MailApp.sendEmail({ // mail participant about experiment
        to: emailAddress,
        subject: subject,
        htmlBody: message
      });
      const emailSentIndex = 10;
      sheet.getRange(startRow + firstSheetParticipantIndex, emailSentIndex).setValue('EMAIL_SENT');
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    } else {
      const subject = 'Research participation';
      MailApp.sendEmail({ // mail participant that he cannot participate
        to: emailAddress,
        subject: subject,
        htmlBody: participantData[0][6]
      });
      sheet.getRange(startRow + firstSheetParticipantIndex, 10).setValue('EMAIL_STUDENT'); // Prevents duplicates
      const range = sheet.getRange(startRow + firstSheetParticipantIndex, 1, 1, 10);
      range.setBackground("red"); // color row in red
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();  
    }
  }
  const pastPar = 'PAST_PAR'
  var sheet = SpreadsheetApp.getActiveSheet();

  // Fetch the range of cells
  const startRow = 2; // First row of data to process
  const numRows = sheet.getLastRow(); // Number of rows to process
  const startCol = 1;
  const numCols = 20;
  const dataRange = sheet.getRange(startRow, startCol, numRows-1, numCols);
  
  // Fetch values for each row in the Range.
  var participantData = dataRange.getValues();
  let lastInsertedSubjectId = participantData[0][0]
  
  participantData.forEach((participant, firstSheetParticipantIndex) => {
    var emailAddress = participant[2]; 
    var emailSent = participant[9];
    var student = participant[6]; 
    var idNumber = participant[7];
    
    const isNewParticipant = getIsNewParticipant(emailSent);
    if (!isNewParticipant) {
      return;
    }

    const hasParticipantBeenInPreviousExperiment = getHasParticipantBeenInPreviousExperiment(
      firstSheetParticipantIndex,
      emailAddress,
      idNumber,
    )
    
    if (!hasParticipantBeenInPreviousExperiment) {
      sendEmail(student, emailAddress, firstSheetParticipantIndex);
    }
  })
}